1. Field of the Invention
This present invention relates generally to handling large result sets in computer systems. More particularly, the present invention relates to efficiently retrieving and paging through large result sets on a computer system.
2. Description of the Related Art
Many computer applications that are built on top of a database system have to implement techniques to handle large database result sets. Examples of such applications include the retrieval and display of data such as customer orders, catalog entries, user access log, etc. These kinds of data are usually massive and the number of database records could range from a couple of thousands to millions in a small scale business operation.
Different techniques can be employed by a web developer to retrieve data from a database and display them on a web page. For example, one can make Java database connectivity (JDBC) calls directly on a Java server pages (JSP) and manipulate the data using logic coded in the JSP. In a web development project that is built on top of a Java 2 Platform enterprise Edition (J2EE) architecture involving the use of Enterprise Java Beans (EJB), the database access logic will be abstracted in the EJB layer. However, in most of the cases, the data retrieved cannot be displayed directly. To make this data accessible and able to be manipulated, the retrieval and display of the data have to be cursored and paginated (e.g. retrieve and display only 50 records at a time, keep track of the current record position, retrieve and display the next set of results when requested). The need for a framework to unify the process is apparent when the following are considered: (a) the number of different ways to achieve the same results (i.e. retrieval and display of data in a paginated fashion); (b) the difficulties in code maintenance when logic is scattered in JSPs and different helper classes; and (c) minimize coding needed as many functions are common and shareable (for example setPageSize, getTotalListSize, see functions on ListDataBeanInterface described later).
There are typical inefficiencies that can arise when a sub-optimal technique is employed. An example of sub-optimal technique is described next. One technique typically employed in many systems is to retrieve all the records, sort the records appropriately, discard the portions that are not needed, and then only display a small amount of the retrieved data. For example, if there were one million records in the database, one million records would be retrieved, and if the 50th record to the 100th record were of interest, those would be extracted and the remaining records discarded. This process would then be repeated when another set of record was needed. This technique is not very efficient because the process retrieves as many records as there are in the database even though only a very small portion are required at a time.
It would therefore be highly desirable to have a method and software allowing large result sets of data to be retrieved efficiently.